﻿using System.Collections.Generic;
using System.Text;
using Dapper;
using Eagles.Application.Model.News.Requset;
using Eagles.Base.Cache;
using Eagles.Base.DataBase;
using Eagles.DomainService.Model.News;
using Eagles.DomainService.Model.User;
using Eagles.Interface.DataAccess;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Logical;

namespace Eagles.DomianService.DataAccess
{
    public class NewsDataAccess : INewsDataAccess
    {
        private readonly IDbManager dbManager;

        private readonly ICacheHelper cacheHelper;

        public NewsDataAccess(IDbManager dbManager, ICacheHelper cacheHelper)
        {
            this.dbManager = dbManager;
            this.cacheHelper = cacheHelper;
        }

        public List<TbNews> GetNewsList(GetNewRequset requset, out int totalCount)
        {
            var sql = new StringBuilder();
            var parameter = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            var token = cacheHelper.GetData<TbUserToken>(requset.Token);
            if (token.OrgId > 0)
            {
                parameter.Append(" and  tbn.OrgId = @OrgId ");
                dynamicParams.Add("OrgId", token.OrgId);
            }


            if (requset.IsExternal > -1)
            {
                parameter.Append(" and  tbn.IsExternal = @IsExternal ");
                dynamicParams.Add("IsExternal", requset.IsExternal);

            }
            if (token.BranchId > 0)
            {
                parameter.Append(" and tbn.BranchId = @BranchId ");
                dynamicParams.Add("BranchId", token.BranchId);
            }

            if (!string.IsNullOrWhiteSpace(requset.NewsName))
            {
                parameter.Append(" and tbn.Title like @Title ");
                dynamicParams.Add("Title", '%'+requset.NewsName  +'%' );
            }

            if (requset.StartTime != null)
            {
                parameter.Append(" and tbn.CreateTime >= @StartTime ");
                dynamicParams.Add("StartTime", requset.StartTime);
            }

            if (requset.EndTime != null)
            {
                parameter.Append(" and tbn.CreateTime <= @EndTime ");
                dynamicParams.Add("EndTime", requset.EndTime);
            }

            

            if (requset.ColumnId > 0)
            {
                parameter.Append(" and tbn.Module = @Module ");
                dynamicParams.Add("Module", requset.ColumnId);
            }

            if (requset.NewsType > 0)
            {
                parameter.Append(" and tbn.NewsType = @NewsType ");
                dynamicParams.Add("NewsType", requset.NewsType);
            }

            if (requset.BranchId > 0)
            {
                parameter.Append(" and tbn.BranchId = @BranchId ");
                dynamicParams.Add("BranchId", requset.BranchId);
            }

            if (requset.PublicOrg > 0)
            {
                parameter.Append(" and tbn.IsPublicOrg = @IsPublicOrg ");
                dynamicParams.Add("IsPublicOrg", requset.PublicOrg);
            }



            if (requset.Status > -2)
            {
                parameter.Append(" and  tbn.`Status` = @Status ");
                dynamicParams.Add("Status", requset.Status);
            }



            sql.AppendFormat(@"SELECT count(*)
FROM `eagles`.`tb_news` as tbn  left join `eagles`.`tb_app_module`  tbam on tbn.Module=tbam.ModuleId  where tbam.SubCateType=0  {0} ;
 ", parameter);
            totalCount = dbManager.ExecuteScalar<int>(sql.ToString(), dynamicParams);

            sql.Clear();

            dynamicParams.Add("pageStart", (requset.PageNumber - 1) * requset.PageSize);
            dynamicParams.Add("pageNum", requset.PageNumber);
            dynamicParams.Add("pageSize", requset.PageSize);

            sql.AppendFormat(@" SELECT tbn.`OrgId`,
    tbn.`NewsId`,
    tbn.`ShortDesc`,
    tbn.BranchId,
    tbn.`Title`,
    tbn.`HtmlContent`,
    tbn.`Author`,
    tbn.`Source`,
    tbn.`Module`,
     tbn.`Status`,
     tbn.`BeginTime`,
     tbn.`EndTime`,
     tbn.`TestId`,
     tbn.`Attach1`,
     tbn.`Attach2`,
     tbn.`Attach3`,
     tbn.`Attach4`,
     tbn.`Attach5`,
     tbn.`OperId`,
     tbn.`CreateTime`,
     tbn.`IsImage`,
     tbn.`IsVideo`,
     tbn.`IsAttach`,
     tbn.`IsClass`,
     tbn.`IsLearning`,
     tbn.`IsText`,
     tbn.`ViewCount`,
     tbn.`ReviewId`,
     tbn.`CanStudy`,
     tbn.`ImageUrl`,
    tbn.IsTop,
 tbn.`StudyTime`,    
   tbn.`RewardsScore`,
 tbn.`NewsType`,
 tb.BranchName,
 tbam.`ModuleName`,
tbn.IsPublicOrg
FROM `eagles`.`tb_news` as tbn  left join `eagles`.`tb_app_module`  tbam on tbn.Module=tbam.ModuleId  
left join tb_branch tb on tb.BranchId=tbn.BranchId

where  ifnull(tbam.SubCateType,0) =0   {0}  order by CreateTime desc limit  @pageStart ,@pageSize
 ", parameter);

            return dbManager.Query<TbNews>(sql.ToString(), dynamicParams);
        }

        public TbNews GetNewsDetail(GetNewDetailRequset requset)
        {

            var sql = new StringBuilder();
            var dynamicParams = new DynamicParameters();

            sql.Append(@"SELECT `tb`.`OrgId`,
    tb.`NewsId`,
    tb.`ShortDesc`,
    tb.`Title`,
    tb.`HtmlContent`,
    tb.`Author`,
    tb.`Source`,
    tb.`Module`,
    tb.`Status`,
    tb.`BeginTime`,
    tb.`EndTime`,
    tb.`TestId`,
    tb.`Attach1`,
    tb.`Attach2`,
    tb.`Attach3`,
    tb.`Attach4`,
    tb.`Attach5`,
    tb.`OperId`,
    tb.`CreateTime`,
    tb.`IsImage`,
    tb.`IsVideo`,
    tb.`IsAttach`,
    tb.`IsClass`,
    tb.`IsLearning`,
    tb.`IsText`,
    tb.`ViewCount`,
    tb.`ReviewId`,
    tb.`CanStudy`,
    tb.`ImageUrl`,
    tb.`IsExternal`,
    tb.`ExternalUrl`,
    tb.`NewsType`,
    tb.`AttachName1`,
    tb.`AttachName2`,
    tb.`AttachName3`,
    tb.`AttachName4`,
    tb.`StudyTime`,    
    tb.`RewardsScore`,
    tb.IsTop,
    tb.IsPublicOrg,
	ttp.TestName
FROM `eagles`.`tb_news` tb left join tb_test_paper ttp on tb.TestId=ttp.TestId
  where tb.NewsId=@NewsId;
 ");
            dynamicParams.Add("NewsId", requset.NewsId);

            return dbManager.QuerySingle<TbNews>(sql.ToString(), dynamicParams);

        }

        public int RemoveNews(RemoveNewRequset requset)
        {

            return dbManager.Excuted(@" DELETE FROM `eagles`.`tb_news` where
                `NewsId` = @NewsId;
", new { requset.NewsId });
        }




        public int EditNews(TbNews mod)
        {
            return dbManager.Excuted(@" UPDATE `eagles`.`tb_news`
SET
`OrgId` = @OrgId,
`ShortDesc` = @ShortDesc,
`Title` = @Title,
`HtmlContent` = @HtmlContent,
`Author` = @Author,
`Source` = @Source,
`Module` = @Module,
`BeginTime` = @BeginTime,
`EndTime` = @EndTime,
`TestId` = @TestId,
`Attach1` = @Attach1,
`Attach2` = @Attach2,
`Attach3` = @Attach3,
`Attach4` = @Attach4,
`OperId` = @OperId,
`IsImage` = @IsImage,
`IsVideo` = @IsVideo,
`IsAttach` = @IsAttach,
`IsClass` = @IsClass,
`IsLearning` = @IsLearning,
`IsText` = @IsText,
`ViewCount` = @ViewCount,
`ReviewId` = @ReviewId,
`CanStudy` = @CanStudy,
`ImageUrl` = @ImageUrl,
`IsExternal` = @IsExternal,
`ExternalUrl` = @ExternalUrl,
`NewsType` = @NewsType,
`AttachName1` = @AttachName1,
`AttachName2` = @AttachName2,
`AttachName3` = @AttachName3,
`AttachName4` = @AttachName4,
 `StudyTime`=@StudyTime,    
`RewardsScore`=@RewardsScore,
IsTop=@IsTop,
`Status` = @Status
WHERE 
`NewsId` = @NewsId



 ", mod);
        }

        public int CreateNews(TbNews mod)
        {
            return dbManager.Excuted(@"INSERT INTO `eagles`.`tb_news`
(`OrgId`,
`BranchId`,
`NewsId`,
`ShortDesc`,
`Title`,
`HtmlContent`,
`Author`,
`Source`,
`Module`,
`Status`,
`BeginTime`,
`EndTime`,
`TestId`,
`Attach1`,
`Attach2`,
`Attach3`,
`Attach4`,
`Attach5`,
`OperId`,
`CreateTime`,
`IsImage`,
`IsVideo`,
`IsAttach`,
`IsClass`,
`IsLearning`,
`IsText`,
`ViewCount`,
`ReviewId`,
`CanStudy`,
`ImageUrl`,
`IsExternal`,
`ExternalUrl`,
`NewsType`,
`AttachName1`,
`AttachName2`,
`AttachName3`,
`AttachName4`,
`StudyTime`,
`RewardsScore`,
`IsTop`,
`IsPublicOrg`)
VALUES
(@OrgId,
@BranchId,
@NewsId,
@ShortDesc,
@Title,
@HtmlContent,
@Author,
@Source,
@Module,
@Status,
@BeginTime,
@EndTime,
@TestId,
@Attach1,
@Attach2,
@Attach3,
@Attach4,
@Attach5,
@OperId,
@CreateTime,
@IsImage,
@IsVideo,
@IsAttach,
@IsClass,
@IsLearning,
@IsText,
@ViewCount,
@ReviewId,
@CanStudy,
@ImageUrl,
@IsExternal,
@ExternalUrl,
@NewsType,
@AttachName1,
@AttachName2,
@AttachName3,
@AttachName4,
@StudyTime,
@RewardsScore,
@IsTop,
@IsPublicOrg);
;



", mod);

        }

        public int ImportNews(List<TbNews> mod)
        {


            return dbManager.Excuted(@"INSERT INTO `eagles`.`tb_news`
(`OrgId`,
`NewsId`,
`ShortDesc`,
`Title`,
`HtmlContent`,
`Author`,
`Source`,
`Module`,
`Status`,
`BeginTime`,
`EndTime`,
`TestId`,
`Attach1`,
`Attach2`,
`Attach3`,
`Attach4`,
`Attach5`,
`OperId`,
`CreateTime`,
`IsImage`,
`IsVideo`,
`IsAttach`,
`IsClass`,
`IsLearning`,
`IsText`,
`ViewCount`,
`ReviewId`,
`CanStudy`,
`ImageUrl`,
`IsExternal`,
`ExternalUrl`,
`NewsType`,
`AttachName1`,
`AttachName2`,
`AttachName3`,
`AttachName4`,
`StudyTime`,    
`RewardsScore`,
`BranchId`)
VALUES
(@OrgId,
@NewsId,
@ShortDesc,
@Title,
@HtmlContent,
@Author,
@Source,
@Module,
@Status,
@BeginTime,
@EndTime,
@TestId,
@Attach1,
@Attach2,
@Attach3,
@Attach4,
@Attach5,
@OperId,
@CreateTime,
@IsImage,
@IsVideo,
@IsAttach,
@IsClass,
@IsLearning,
@IsText,
@ViewCount,
@ReviewId,
@CanStudy,
@ImageUrl,
@IsExternal,
@ExternalUrl,
@NewsType,
@AttachName1,
@AttachName2,
@AttachName3,
@AttachName4,
@StudyTime,    
@RewardsScore,
@BranchId);

", mod);


        }

        public List<TbNews> GetNewsList(List<int> list)
        {
            var sql = new StringBuilder();
            var dynamicParams = new DynamicParameters();

            sql.Append(@"  SELECT `tb_news`.`OrgId`,
    `tb_news`.`NewsId`,
    `tb_news`.`ShortDesc`,
    `tb_news`.`Title`,
    `tb_news`.`HtmlContent`,
    `tb_news`.`Author`,
    `tb_news`.`Source`,
    `tb_news`.`Module`,
    `tb_news`.`Status`,
    `tb_news`.`BeginTime`,
    `tb_news`.`EndTime`,
    `tb_news`.`TestId`,
    `tb_news`.`Attach1`,
    `tb_news`.`Attach2`,
    `tb_news`.`Attach3`,
    `tb_news`.`Attach4`,
    `tb_news`.`Attach5`,
    `tb_news`.`OperId`,
    `tb_news`.`CreateTime`,
    `tb_news`.`IsImage`,
    `tb_news`.`IsVideo`,
    `tb_news`.`IsAttach`,
    `tb_news`.`IsClass`,
    `tb_news`.`IsLearning`,
    `tb_news`.`IsText`,
    `tb_news`.`ViewCount`,
    `tb_news`.`ReviewId`,
    `tb_news`.`CanStudy`,
    `tb_news`.`ImageUrl`,
    `tb_news`.`IsExternal`,
    `tb_news`.`ExternalUrl`,
    `tb_news`.`StudyTime`,    
    `tb_news`.`RewardsScore`
FROM `eagles`.`tb_news`
  where NewsId in @NewsId; 
 ");
            dynamicParams.Add("NewsId", list.ToArray());

            return dbManager.Query<TbNews>(sql.ToString(), dynamicParams);
        }

      

        public List<TbUserNews> GetNewsList(string token, int type , int branchId, out int totalCount)
        {
            var sql = new StringBuilder();
            var parameter = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            var userToken = cacheHelper.GetData<TbUserToken>(token);

            parameter.Append(" and  tbun.OrgId = @OrgId ");
            dynamicParams.Add("OrgId", userToken.OrgId);

            //parameter.Append(" and  NewsType = @NewsType ");
            //dynamicParams.Add("NewsType", 1);

            if (branchId > 0)
            {
                parameter.Append(" and  tbun.BranchId = @branchId ");
                dynamicParams.Add("branchId", branchId);
            }
         
            //机构
            if (type == 1)
            {
                parameter.Append(" and  tbun.OrgReview = -1 ");
                parameter.Append(" and  tbun.BranchReview = 0 ");
                parameter.Append(" and  tbun.IsPublic = 3 ");//查询支部的时候，公开到机构，才可以被查询出来
            }
            else if (type == 2)
            {
                dynamicParams.Add(" and  tbun.BranchId = @BranchId ", userToken.BranchId);
                parameter.Append(" and  tbun.BranchReview = -1 ");
                parameter.Append(" and  (tbun.IsPublic = 2 or tbun.IsPublic=3) ");//查询支部的时候，等于机构或者支部审核，都可以被查询出来
            }


            sql.AppendFormat(@"SELECT count(*)
FROM eagles.tb_user_news  tbun inner join eagles.tb_user_info tbui on tbun.UserId=tbui.UserId   where 1=1  {0} ;
 ", parameter);
            totalCount = dbManager.ExecuteScalar<int>(sql.ToString(), dynamicParams);

            sql.Clear();



            sql.AppendFormat(@" SELECT tbun.OrgId,
    tbun.BranchId,
    tbun.NewsId,
    tbun.UserId,
    tbun.ToUser,
    tbun.Title,
    tbun.HtmlContent,
    tbun.NewsType,
    tbun.Status,
    tbun.CreateTime,
    tbun.ViewsCount,
    tbun.RewardsScore,
    tbun.ReviewId,
    tbun.OrgReview,
    tbun.BranchReview,
    tbun.IsPublic,
    tbun.PublicTime
FROM eagles.tb_user_news  tbun inner join eagles.tb_user_info tbui on tbun.UserId=tbui.UserId 
  where  1=1 {0}   order by tbun.CreateTime desc
 ", parameter);

            return dbManager.Query<TbUserNews>(sql.ToString(), dynamicParams);

        }

        public int SetNewsPublicOrg(SetNewsPublicOrgRequset requset)
        {
            return dbManager.Excuted(@" UPDATE `eagles`.`tb_news`
SET
`IsPublicOrg` = 1 
WHERE 
`NewsId` = @NewsId
", new { requset.NewsId });
        }

       
    }
}
